<?php
// +----------------------------------------------------------------------
// | student[ WE CAN DO IT JUST THINK IT ]
// +----------------------------------------------------------------------
// | Copyright (c) 2018 http://www.onethink.cn All rights reserved.
// +----------------------------------------------------------------------
// | Author: 水月居 <singliang@163.com> <http://blog.sina.com.cn/shuiyueju8/>
// +----------------------------------------------------------------------

/**
 * excel导入导出函数
 * 主要定义前台公共函数库
 */

/*过滤掉特殊字符为表名，防止因名表中含有特殊字符而生成excel错误*/
function replaceSpecialChar($strParam){
    $regex = "/\/|\~|\!|\@|\#|\\$|\%|\^|\&|\*|\+|\{|\}|\:|\<|\>|\?|\[|\]|\,|\.|\/|\;|\'|\`|\=|\\\|\|/";
    return preg_replace($regex,"_",$strParam);
}
function replaceSheetNameChar($strParam){
    $regex = "/\/|\~|\!|\@|\#|\\$|\%|\^|\&|\*|\+|\{|\}|\:|\<|\>|\?|\[|\]|\,|\.|\/|\;|\'|\`|\=|\\\|\|/";
    return preg_replace($regex,"_",$strParam);
}

    /**
     * @param  [type] $xlsdata  [导出数据]
     * @param  [type] $savefile [导出的文件名]
     * @param  [array] $format  [单元格宽度高度等设置]
     * @param  string $pageset  [页面是否为横向]
     * @return [type]           [导出excel文件]
     */
function exportOneExcelByTitle($xlsdata,$savefile=null,$properties=null )
    {
        if(!is_array($xlsdata)){return false;}
        //若没有指定文件名则为当前时间戳
        if(is_null($savefile)){
            $savefile=time();
        }
        //导入phpExcel类
        vendor("PHPExcel");
        $objPHPExcel = new \PHPExcel();
        // dump($properties)
         
         /*文件属性设置 **/        
            $file_creator=$properties['creator']?$properties['creator']:'水月居';
            $file_category=$properties['category']?$properties['category']:'excel表格';
            $objPHPExcel->getProperties()  
                             ->setCreator($file_creator) //设置创建者  
                             ->setLastModifiedBy(date('Y-m-d',time())) //设置时间  
                             ->setTitle('Office 2013 天台县外国语学校') //设置标题  
                             ->setSubject($file_category) //设置备注  
                             ->setDescription('ttwgy天台县外国语学校小学部') //设置描述  
                             ->setKeywords($file_creator) //设置关键字 | 标记  
                             ->setCategory($file_category); //设置类别 
       

        $sheetIndex = 0;
        // dump($xlsdata);

        foreach ($xlsdata as $k => $Adata) {
        	// dump($Adata);die;

            $sheetname = $Adata['sheetname'];
            $header = $Adata['header'];
            $title = $Adata['title'];
            $format = $Adata['format'];
            $data = $Adata['data'];
            $pageset = $Adata['$pageset'];
             // dump($width);die;

            //判断data为空时不执行，防止死机
            if(!$data){
                echo '<span style="color:red">没有你想要导出的数据</span>';
                exit;
            }

            $allcol = count($data[0])-1; //取总列数
            //计算列数字符
            $allcolABC = PHPExcel_Cell::stringFromColumnIndex($allcol);

            //多表
            if($sheetIndex>0){
                $objPHPExcel->createSheet();
            }
            $obj=$objPHPExcel->setActiveSheetIndex($sheetIndex);
            //是否设置页面为横向
            if($pageset['orientation']){
               $obj->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE); 
            }

            /*样式默认设置项目*/
            $pagesize= $pageset['paperSize']?'PAPERSIZE_'.$pageset['paperSize']:'PAPERSIZE_A4';//设置纸张大小为A4
            $obj->getPageSetup()->setPaperSize('PHPExcel_Worksheet_PageSetup:: '.$pagesize); 
            $obj->getDefaultStyle()->getFont()->setName('宋体');
            $obj->getDefaultStyle()->getFont()->setSize('10');
            $defaultRowHeight = $pageset['defaultRowHeight']?$pageset['defaultRowHeight']:20;//默认行高设置
            $obj->getDefaultRowDimension()->setRowHeight($defaultRowHeight);
            $defaultWidth = $pageset['defaultWidth']?$pageset['defaultWidth']:6;//设置默认列宽
            $obj->getDefaultColumnDimension()->setWidth("$defaultWidth");
            $obj->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
                                          ->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//设置水平、垂直居中
            //写入头部标题
            $start_row =count($header)+1; 

            if(is_array($header)){
                
                foreach($header as $m=>$v){
                  $m++;
                  $obj->mergeCells('A'.$m.':'.$allcolABC.$m);
                  $obj->setCellValue('A'.$m,$v); //写入头部总标题 
                                  $obj->getStyle('A'.$m)->applyFromArray(array(
                    'font' => array('size' => 12,'bold' => false,'name' => '宋体' ),                 
                    'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
                )); 
                }

                //头部标题加粗居中
                $obj->getStyle('A1')->applyFromArray(array(
                    'font' => array('size' => 18,'bold' => true ),                 
                    'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
                ));     
            }
            //设置数据列宽
            // $width=$w['datawidth']; ["defaultRowHeight"] => string(2) "18"
            // ["defaultWidth"] => string(1) "7"
            // ["datawidth"]
            $width=$format['datawidth'];
            if(is_array($width)){
                for ($i=0;$i<count($width);$i++){ 
                //计算列字符
                $col = PHPExcel_Cell::stringFromColumnIndex($i);    
                    if(is_null($width)){
                        $obj->getColumnDimension($col)->setAutoSize(true);
                    }else{
                        if($width[$i]=='auto'){
                            $obj->getColumnDimension($col)->setAutoSize(true);
                        }else{
                            $obj->getColumnDimension($col)->setWidth($width[$i]);
                        }                     
                    }
                }           
            }
            //若指字了excel表头则添加表头
            //
            //表头样式设置数组
            $title_style=array(
                        'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
                        'font' => array(
                                'bold' => true,
                                'color' => array('rgb' => '000000'),
                                'size' => 10,
                                'name' => '宋体'
                        ),
                       'fill' => array (
                              'type'=>PHPExcel_Style_Fill::FILL_SOLID,                             
                              'startcolor' => array (
                                   'argb' => 'FFD9E1F9'
                               ),

                        )
                    );            
            if(is_array($title)){
                $row=$start_row;
                $tNum=1;
                $nn=0;
                foreach($title as $v){
                  $col = PHPExcel_Cell::stringFromColumnIndex($nn); 
                  $obj->setCellValue($col.$row,$v);
                  //自动转换显示字体大小,使内容能够显示
                  $obj->getStyle($col.$row)->getAlignment()->setShrinkToFit(true);
                  $obj->getStyle($col.$row)->applyFromArray($title_style);//表头样式设置
                  $nn++; 
                }
               
            }else{
                $tNum=0;
            }
            //数据表格格式设置+加外边框
            $data_style=array(
                'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)),
                'font' => array(
                        'bold' => false,
                        'color' => array('rgb' => '000000'),
                        'size' => 10,
                        'name' => '宋体'
                       )
            );
                    

            foreach($data as $k => $v){              
                $row=$k+$start_row+$tNum;//数据开始行
                $nn=0;
                foreach($v as $vv){
                    $col = PHPExcel_Cell::stringFromColumnIndex($nn);
                    //防止出现科学计数
                    if(is_numeric($vv) && strlen($vv)>11){
                        $obj->setCellValueExplicit($col.$row,$vv,PHPExcel_Cell_DataType::TYPE_STRING);
                        $obj->getStyle($col.$row)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
                    }else{
                        $obj->setCellValue($col.$row,$vv);
                    }
                    $obj->getStyle($col.$row)->applyFromArray($data_style);
                    $nn++;
                }

            }
            $obj->setTitle($sheetname);
            $sheetIndex++; //下一张表
        }
        $objPHPExcel->setActiveSheetIndex(0);
        $filename=$savefile.'.xlsx';
        $agent = $_SERVER["HTTP_USER_AGENT"]; 
        
        // dump($agent) ;die;
        $encoded_filename = urlencode($filename);        
        header('Content-Type: application/vnd.ms-excel');
        header('Cache-Control: max-age=0');
        if (preg_match('/FireFox\/([^\s]+)/i', $agent)) {
            // $filename='firefox'.$filename;
            header('Content-Disposition: attachment; filename="' . $filename . '"');
        } else if (preg_match('/Chrome\/([^\s]+)/i', $agent)) { 
            // $encoded_filename='chrome'.$encoded_filename;
        header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');
        } else {
        $encoded_filename = str_replace("+", "%20", $encoded_filename);
        header('Content-Disposition: attachment; filename="' . $encoded_filename . '"');        
        } 
        
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        exit;

    }
/**
 * 用于导入excel文件返回数组
 * @author:shuiyue8
 * @param $data 读取excel的数据(二维数组形式)
 * @param null $savefile 生成excel的文件名(不指定,则为当前时间戳)
 * @param $file:导入的文件名，$exts文件扩展名
 * @param $datarow标题是第几行开始读取数据
 *
 */
function readexcel2arryByTitle($filename,$exts,$datarow=1){

        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        vendor("PHPExcel"); 
        //创建PHPExcel对象，注意，不能少了\ 
        $objPHPExcel = new \PHPExcel();  
        //如果excel文件后缀名为.xls，导入这个类
        if($exts == 'xls'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel5'); 
        }else if($exts == 'xlsx'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else{
            $this->error('不支持此文件类型导入，仅支持xls与xlsx格式的文件导入');
        }
        $objReader->setReadDataOnly(true);   
        //载入文件
        $objPHPExcel = $objReader->load($filename); 
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet=$objPHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        $currentRow = $datarow;
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $cell=$currentSheet->getCell($address)->getValue(); 

                if($cell instanceof PHPExcel_RichText){//富文本转换字符串
                    $cell  = $cell->__toString();
                }
                if($cell){
                   $column[]=$currentColumn;
                   $title[$currentColumn]=$cell;   
                }
                
            }
        //$data['title']=$title;
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for($currentRow=$datarow+1;$currentRow<=$allRow;$currentRow++){
            //从哪列开始，A表示第一列
            foreach($column as $currentColumn){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $cell=$currentSheet->getCell($address)->getValue();                
                if($cell instanceof PHPExcel_RichText){//富文本转换字符串
                    $cell  = $cell->__toString();
                }
                $data[$currentRow][$currentColumn]=$cell;
                //print_r($cell);
            } 
            //从哪列开始，A表示第一列
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $cell=$currentSheet->getCell($address)->getValue();                
                if($cell instanceof PHPExcel_RichText){//富文本转换字符串
                    $cell  = $cell->__toString();
                }
                $data2[$currentRow][$currentColumn]=$cell;
                //print_r($cell);
            } 
        }  
        $data=array(
            'column'=>$column,
            'title'=>$title,
            'data'=>$data
            );
        return $data;
}

/**
 * 用于导入excel文件返回数组
 * author:walker
 * @param $data 读取excel的数据(二维数组形式)
 * @param null $savefile 生成excel的文件名(不指定,则为当前时间戳)
 * @param $file:导入的文件名，$exts文件扩展名
 * @param $datarow从第几行开始读取数据
 *
 */
function readexcel2arry($filename,$exts,$datarow=1){

        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        vendor("PHPExcel"); 
        //创建PHPExcel对象，注意，不能少了\ 
        $objPHPExcel = new \PHPExcel();  
        //如果excel文件后缀名为.xls，导入这个类
        if($exts == 'xls'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel5'); 
        }else if($exts == 'xlsx'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else{
        	$this->error('不支持此文件类型导入，仅支持xls与xlsx格式的文件导入');
        }
        $objReader->setReadDataOnly(true);   
        //载入文件
        $objPHPExcel = $objReader->load($filename); 
        //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
        $currentSheet=$objPHPExcel->getSheet(0);
        //获取总列数
        $allColumn=$currentSheet->getHighestColumn();
        //获取总行数
        $allRow=$currentSheet->getHighestRow();
        $currentRow = $datarow;
        //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
        for($currentRow=$datarow;$currentRow<=$allRow;$currentRow++){
            //从哪列开始，A表示第一列
            for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
                //数据坐标
                $address=$currentColumn.$currentRow;
                //读取到的数据，保存到数组$arr中
                $cell=$currentSheet->getCell($address)->getValue();                
                if($cell instanceof PHPExcel_RichText){//富文本转换字符串
                    $cell  = $cell->__toString();
                }
                $data[$currentRow][$currentColumn]=$cell;
                //print_r($cell);
            } 
        }  
        return $data;
}
